package excel.study.test;

import org.apache.commons.collections4.MapUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {
    public static void main(String[] args) throws IOException {
        List<String> title = Arrays.asList("ceshi", "ceshi2");
        FileOutputStream fileOutputStream = null;
        try {
             fileOutputStream = new FileOutputStream("D:\\home\\excel\\test1.xlsx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        Workbook workbook = ExcelUtil.createExcel(title,null,fileOutputStream);
    }


    public static void Excel(){
        // 创建一个excel
        XSSFWorkbook book = new XSSFWorkbook();
        // 创建需要用户填写的sheet
        XSSFSheet sheetPro = (XSSFSheet) book.createSheet("SheetName");
        //设置前x行冻结
        sheetPro.createFreezePane(0,1,0,1);
        // 产生表格标题行
        Row row0 = sheetPro.createRow(0);

        row0.createCell(0).setCellValue("title01");
        //title02需要下拉
        row0.createCell(1).setCellValue("title02");
        row0.createCell(2).setCellValue("title03");

        //下拉值，设置下拉控制的范围
        CellRangeAddressList regions = new CellRangeAddressList(1, 50, 1, 1);
        // 生成下拉框内容
        // 绑定下拉框和作用区域
        String[] strings = new String[] { "列1", "列2", "列3"};
        //设置默认文件名为当前时间：年月日时分秒
        String fileName=new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString();

    }



    public static Workbook createExcel(List<String> header, List<String[]> data, OutputStream out) throws IOException {
        // 创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作表
        HSSFSheet sheet = workbook.createSheet("ceshi");
        // 添加表头行
        HSSFRow hssfRow = sheet.createRow(0);
        // 设置单元格格式居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 添加表头内容
        for (int i = 0; i < header.size(); i++) {
            HSSFCell headCell = hssfRow.createCell(i);
            headCell.setCellValue(header.get(i));
            headCell.setCellStyle(cellStyle);
        }

        //设置下拉列表
        Sheet data_validation = workbook.createSheet("Data Validation");
        CellRangeAddressList addressList = new CellRangeAddressList(
                0, 0, 0, 0);
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                new String[]{"10", "20", "30"});
        DataValidation dataValidation = new HSSFDataValidation
                (addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.createErrorBox("提示", "不允许自己输入，请选择下拉框里的数据");
        dataValidation.createPromptBox("提示", "请选择下拉框里的数据");
        dataValidation.setShowPromptBox(true);
        data_validation.addValidationData(dataValidation);

        // 添加数据内容
        for (int i = 0; i < data.size(); i++) {
            String[] strings = data.get(i);
            hssfRow = sheet.createRow(i + 1);
            for (int j = 0; j < strings.length; j++) {
                HSSFCell cell = hssfRow.createCell(j);
                cell.setCellValue(strings[j]);
                cell.setCellStyle(cellStyle);
            }
        }
        //单元格自适应
        sheet.autoSizeColumn(1,true);
        // 保存Excel文件
        workbook.write(out);
        return workbook;
    }

    /**
     * 创建表格头
     *
     * @param wb
     * @param sheetName
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> Sheet createHeader(Workbook wb, String sheetName, Class<T> clazz) {
        Sheet sheet = wb.createSheet(sheetName);
        Row row = sheet.createRow(0);
        Field[] fields = clazz.getDeclaredFields();
        Cell cell;
        CellStyle style = getStringCenterStyle(wb);
        for (Field field : fields) {
            PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
            if (poiExcel == null) {
                continue;
            }
            cell = row.createCell(poiExcel.index());
            cell.setCellStyle(style);//设置单元格格式为"文本"
            cell.setCellType(CellType.STRING);
            cell.setCellValue(poiExcel.value());
        }
        return sheet;
    }

    /**
     * 样式
     *
     * @param wb
     * @return
     */
    private static CellStyle getStringCenterStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        return style;
    }



    /**
     * 创建下拉数据
     *
     * @param wb
     * @param data
     */
    private static <T> void createDropDownData(Workbook wb, Sheet sheet, T data) {
        Class clazz = data.getClass();
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.asList(fields);
        boolean tag = wb.getSheet("hidden") == null;
        Sheet hiddenSheet = wb.getSheet("hidden") == null ? wb.createSheet("hidden") : wb.getSheet("hidden");
        wb.setSheetHidden(1, true);

        fieldList.stream().forEach(field -> {
            field.setAccessible(true);
            PoiExcel poiExcel = field.getAnnotation(PoiExcel.class);
            if (poiExcel != null) {
                if (List.class.isAssignableFrom(field.getType())) {
                    String[] dropDownDatas;
                    Type type = field.getGenericType();
                    if (type instanceof ParameterizedType) {
                        try {
                            Object fieldData = field.get(data);
                            if (fieldData != null) {
                                if (tag) {
                                    Class StringClazz = fieldData.getClass();//获取到属性的值的Class对象
                                    Method m = StringClazz.getDeclaredMethod("size");
                                    int size = (Integer) m.invoke(fieldData);//调用list的size方法，得到list的长度
                                    dropDownDatas = new String[size];
                                    for (int i = 0; i < size; i++) {//遍历list，调用get方法，获取list中的对象实例
                                        Method getM = StringClazz.getDeclaredMethod("get", int.class);
                                        getM.setAccessible(true);
                                        dropDownDatas[i] = getM.invoke(fieldData, i).toString();
                                    }

                                    Row row = hiddenSheet.createRow(poiExcel.index());
                                    for (int i = 0; i < dropDownDatas.length; i++) {
                                        Cell cell = row.createCell(i);
                                        cell.setCellValue(dropDownDatas[i]);
                                    }
                                    createDataName(wb, "list" + poiExcel.index(), "hidden!$A$" + (poiExcel.index() + 1) + ":$" + getPos(dropDownDatas.length)
                                            + "$" + (poiExcel.index() + 1));
                                }

                                DataValidation data_validation = createDataValidation("list" + poiExcel.index(), 1, 65533,
                                        poiExcel.index(), poiExcel.index());
                                sheet.addValidationData(data_validation);
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                }

                if (Map.class.isAssignableFrom(field.getType())) {
                    Type type = field.getGenericType();
                    if (type instanceof ParameterizedType) {
                        try {
                            Object fieldData = field.get(data);
                            if (fieldData != null) {
                                if (tag) {
                                    Class mapClazz = fieldData.getClass();//获取到属性的值的Class对象
                                    Row row = hiddenSheet.createRow(poiExcel.index());
                                    int from = 0;//标识在行数据中级联的起始列和终止列
                                    int end = 0;
                                    Method entrySetM = mapClazz.getDeclaredMethod("entrySet");
                                    Set<Map.Entry> entrySet = (Set<Map.Entry>) entrySetM.invoke(fieldData);
                                    Cell cell;
                                    for (Map.Entry entry : entrySet) {
                                        List<String> listValue = (List<String>) entry.getValue();
                                        for (String s : listValue) {
                                            cell = row.createCell(end++);
                                            cell.setCellValue(s);
                                        }
                                        //循环创建子序列名
                                        createDataName(wb, entry.getKey().toString(), "hidden!$" + getPos(from + 1)
                                                + "$" + (poiExcel.index() + 1) + ":$" + getPos(end) + "$" + (poiExcel.index() + 1));
                                        from = end;
                                    }
                                }
                                for (int j = 1; j < 102; j++) {
                                    DataValidation data_validation = createDataValidation("INDIRECT($" + getPos(poiExcel.ref() + 1) + "$" + (j + 1) + ")",
                                            j, j, poiExcel.index(), poiExcel.index());
                                    sheet.addValidationData(data_validation);
                                }
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        });
    }

    /**
     * 创建命名数据
     */
    private static void createDataName(Workbook wb, String name, String region) {
        Name namedCell = wb.createName();
        namedCell.setNameName(name);
        namedCell.setRefersToFormula(region);
    }

    /**
     * 根据输入的数字返回excel列数字符
     *
     * @param size
     * @return
     */
    private static String getPos(int size) {
        if (size <= 0) {
            return "A";
        }
        String[] args = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S",
                "T", "U", "V", "W", "X", "Y", "Z"};
        boolean tag = true;//用来结束循环
        StringBuffer reversepos = new StringBuffer();
        while (tag) {
            int pos = size % 26;
            if (pos == 0) {
                pos = 25;//整除说明是26结尾，故为Z
                size--;//之所以size会减1是因为列并没有数字中所谓的10这回事，Z后面就是AA
            } else {
                pos -= 1;
            }
            int result = size / 26;
            if (result == 0) {
                reversepos.append(args[pos]);
                tag = false;
            } else {
                reversepos.append(args[pos]);
                size /= 26;
            }
        }
        return reversepos.reverse().toString();//因为是从类似个位开始算起来，所以要倒转
    }


    /**
     * 创建数据约束
     *
     * @param name
     * @param fromRow
     * @param endRow
     * @param fromColumn
     * @param endColumn
     * @return
     */
    private static DataValidation createDataValidation(String name, int fromRow, int endRow, int fromColumn, int endColumn) {
        //起始行，终止行，起始列，终止列
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, fromColumn, endColumn);
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(name);
        return new HSSFDataValidation(regions, constraint);
    }



    public static void createExcel(String title,List<String> header, List<String[]> data, OutputStream out) throws IOException {
        // 创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作表
        HSSFSheet sheet = workbook.createSheet("sheet1");
        // 添加表头行
        HSSFRow hssfRow = sheet.createRow(0);
        HSSFCell cellTitle = hssfRow.createCell(0);
        // 设置标题外的单元格格式居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置标题的样式
        HSSFCellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置标题字体的样式
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);//设置字体大小
        titleCellStyle.setFont(font);
        //标题设置(四个参数分别表示起始行，终止行，起始列，终止列)
        cellTitle.setCellValue(title);
        int lastCol = header.size() > 1 ? header.size() : 2;
        CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) lastCol - 1);
        sheet.addMergedRegion(region1);
        hssfRow = sheet.createRow(1);
        hssfRow = sheet.createRow(2);
        cellTitle.setCellStyle(titleCellStyle);
        // 添加表头内容
        for (int i = 0; i < header.size(); i++) {
            HSSFCell headCell = hssfRow.createCell(i);
            headCell.setCellValue(header.get(i));
            headCell.setCellStyle(cellStyle);
        }

        // 添加数据内容
        for (int i = 0; i < data.size(); i++) {
            String[] strings = data.get(i);
            hssfRow = sheet.createRow(i + 3);
            for (int j = 0; j < strings.length; j++) {
                HSSFCell cell = hssfRow.createCell(j);
                cell.setCellValue(strings[j]);
                cell.setCellStyle(cellStyle);
            }
        }

        // 保存Excel文件
        workbook.write(out);
    }

    /**
     * 读取Excel的内容
     *
     * @param fileType 文件类型，xls或xlsx
     * @param startRows 开始读取行数，比喻行头不需要读入 忽略的行数为1
     * @param ignoreRowBlank 是否忽略空行
     * @param is 文件输入流
     * @return 读出的Excel中数据的内容
     * @throws IOException duxxxxx
     */
    public static List<String[]> readData(String fileType, int startRows, boolean ignoreRowBlank, InputStream is) throws IOException {
        List<String[]> result = new ArrayList<>();

        XSSFWorkbook wb = new XSSFWorkbook(is);
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            Sheet sheet = wb.getSheetAt(sheetIndex);

            for (int rowIndex = startRows, z = sheet.getLastRowNum(); rowIndex <= z; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }

                int rowSize = row.getLastCellNum();
                String[] values = new String[rowSize];
                boolean hasValue = false;
                for (int columnIndex = 0; columnIndex < rowSize; columnIndex++) {
                    String value = "";
                    Cell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                value = String.valueOf(cell.getNumericCellValue());
                                break;
                            case FORMULA:
                                // 导入时如果为公式生成的数据则无值
                                /*if (!cell.getStringCellValue().equals("")) {
                                    value = cell.getStringCellValue();
                                } else {
                                    value = cell.getNumericCellValue() + "";
                                }*/
                                break;
                            case BOOLEAN:
                                value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                                break;
                            default:
                                value = "";
                        }
                    }
                    values[columnIndex] = value;
                    if (!value.isEmpty()) {
                        hasValue = true;
                    }
                }
                if (!ignoreRowBlank || hasValue) {//不为忽略空行模式或不为空行
                    result.add(values);
                }
            }
        }
        return result;
    }

    //读取excel
    private static Workbook readExcel(String fileType, InputStream is) throws IOException {
        if ("xls".equals(fileType)) {
            return new HSSFWorkbook(is);
        } else if ("xlsx".equals(fileType)) {
            return new XSSFWorkbook(is);
        } else {
            throw new IllegalArgumentException("不支持的文件类型，仅支持xls和xlsx");
        }
    }

    /**
     * 去掉字符串右边的空格
     *
     * @param str 要处理的字符串
     * @return 处理后的字符串
     */
    private static String rightTrim(String str) {
        if (str == null) {
            return "";
        }
        int length = str.length();
        for (int i = length - 1; i >= 0; i--) {
            if (str.charAt(i) != 0x20) {
                break;
            }
            length--;
        }
        return str.substring(0, length);
    }


    /**
     * @Author like
     * @Description: 解析 Excel内容,map 可以转换成对象保存
     * @Date 13:32 2020/9/22
     * @Param [title, is, fileType]
     * @Return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
    **/
    public static List<Map<String,Object>> parseExcel(List<String> title, InputStream is,String fileType) throws IOException {
        List<Map<String,Object>> results = new ArrayList<>();

        Workbook sheets = readExcel(fileType, is);

        Sheet sheet = sheets.getSheetAt(0);
        for (int i = 1; i < sheet.getLastRowNum()+1; i++) {
            Map<String, Object> map = new HashMap<>();
            //去掉首行 标题开始算 row
            Row currentRow = sheet.getRow(i);
            if(null == currentRow){
                continue;
            }
            for (int j = 0; j < title.size(); j++) {
                Cell currentRowCell = currentRow.getCell(j);
                String currentTitle = title.get(j);
                String cellValue = getCellStringVal(currentRowCell);
                if (StringUtils.isNotEmpty(cellValue)){
                    map.put(currentTitle,cellValue);
                }

            }
            if (MapUtils.isNotEmpty(map)){
                results.add(map);
            }

            map.put("currentRow",i+1);
        }
        return results;

    }

    private static String getCellStringVal(Cell cell) {
        if (cell==null){
            return "";
        }
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case NUMERIC:
                DecimalFormat df = new DecimalFormat("#");
                return df.format(cell.getNumericCellValue());
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default:
                return StringUtils.EMPTY;
        }
    }

    /**
     * @Author like
     * @Description: 解析 Excel内容,map 可以转换成对象保存
     * @Date 13:32 2020/9/22
     * @Param [title, is, fileType]
     * @Return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     **/
    public static byte[] exportExcel(List<String> headerList, Map<String,String> titleMap,
                                                       List<Map<String,String>> asserts) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("1");

        //写 sheel 头
        Row headerRow = sheet.createRow(0);

        for (int i = 0; i < headerList.size(); i++) {
            Cell headerCell = headerRow.createCell(i);
            headerCell.setCellValue(titleMap.get(headerList.get(i)));

            //修改 sheel 头的样式
            /*CellStyle cellStyle = workbook.createCellStyle();
            CreationHelper creationHelper = workbook.getCreationHelper();
            cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
            headerRow.setRowStyle(cellStyle);*/
        }

        //写 表的数据 第一行 背占用
        for (int j = 0; j < asserts.size(); j++) {

            Row assertRow = sheet.createRow(j+1);

            for (int k = 0; k < headerList.size(); k++) {
                Cell headerCell = assertRow.createCell(k);
                headerCell.setCellValue(asserts.get(j).get(headerList.get(k)));
            }
        }

        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } finally {
            bos.close();
        }

        return bos.toByteArray();

    }


}
